# -*- coding: utf-8 -*-
# @Time : 2021-04-25 18:59
# @Author : zj
# @File : class_hw17.py
# @Software: PyCharm
import pymysql
from pymysql import *


def create_table():
    db = pymysql.connect(host='49.233.39.160',
                         user='user',
                         db='lebo16',
                         port=3306,
                         passwd='leboAa!#$123',
                         charset='utf8')
    cursor = db.cursor()
    cursor.execute('drop table if exists stu_info;')
    sql = """create table stu_info (
    studentNo int auto_increment primary key,
    name varchar(10),
    sex varchar(1),
    hometown varchar(20),
    age tinyint(4),
    class varchar(10),
    card varchar(20)
    ) ;"""
    try:
        cursor.execute(sql)
        print("数据库创建成功")
    except Exception as e:
        print("数据库创建失败，原因：", e)
    finally:
        cursor.close()
        db.close()


def insert_table():
    db = pymysql.connect(host='49.233.39.160', user='user', passwd='leboAa!#$123', db='lebo16', port=3306)
    cursor = db.cursor()
    sql = """insert into stu_info values
('001', '王昭君', '女', '北京', '20', '1班', '340322199001247654'),
('002', '诸葛亮', '男', '上海', '18', '2班', '340322199002242354'),
('003', '张飞', '男', '南京', '24', '3班', '340322199003247654'),
('004', '白起', '男', '安徽', '22', '4班', '340322199005247654'),
('005', '大乔', '女', '天津', '19', '3班', '340322199004247654'),
('006', '孙尚香', '女', '河北', '18', '1班', '340322199006247654'),
('007', '百里玄策', '男', '山西', '20', '2班', '340322199007247654'),
('008', '小乔', '女', '河南', '15', '3班', null),
('009', '百里守约', '男', '湖南', '21', '1班', ''),
('010', '妲己', '女', '广东', '26', '2班', '340322199607247654'),
('011', '李白', '男', '北京', '30', '4班', '340322199005267754'),
('012', '孙膑', '男', '新疆', '26', '3班', '340322199000297655')"""
    try:
        cursor.execute(sql)
        db.commit()
        print('数据插入成功')
    except Exception as e:
        print('数据插入失败，原因：', e)
        db.rollback()
    finally:
        cursor.close()
        db.close()


def search_table():
    db = connect(host='49.233.39.160', user='user', passwd='leboAa!#$123', db='lebo16', port=3306, charset='utf8')
    cursor = db.cursor(cursor=cursors.DictCursor)

    name = input('输入name：')
    age = int(input('输入age:'))
    sql = "select * from stu_info where name=%s and age=%s;"
    count = cursor.execute(sql, [name, age])

    print('查询到%d条数据' % count)

    ret = cursor.fetchmany(5)
    print(ret)
    cursor.close()
    db.close()


while True:
    db = pymysql.connect(host='49.233.39.160',
                         user='user',
                         db='lebo16',
                         port=3306,
                         passwd='leboAa!#$123',
                         charset='utf8')
    cur = db.cursor(pymysql.cursors.DictCursor)

    pd = int(input("输入1/2/3/4/5:"))
    if pd == 1:
        print("注册")
        user = input("user:")
        card = input("card:")
        sql = 'select * from stu_info where card = %s and name = %s;'
        count = cur.execute(sql, [card, user])
        if count == 1:
            print("已存在")
        else:
            sql = 'insert into stu_info(card, name) values(%s, %s)'
            print(sql)
            cur.execute(sql, [card, user])
            db.commit()
            print("注册成功")
        cur.close()

    elif pd == 2:
        print("登录")
        user = input("user:")
        card = input("card:")
        sql = 'select * from stu_info where card = %s and name = %s;'
        count = cur.execute(sql, [card, user])
        if count == 1:
            print("登录成功")
            ret = cur.fetchone()
            print(ret)
        else:
            print("错误")
        cur.close()

    elif pd == 3:
        print("修改")
        card = input("card:")
        sql = 'select * from stu_info where card = %s;'
        count = cur.execute(sql, card)
        if count == 1:
            new_age = input("请输入age:")
            sql = 'update stu_info set age = %s where card = %s;'
            cur.execute(sql, [new_age, card])
            db.commit()
            print("修改成功")
        else:
            print("查无此card")
        db.close()

    elif pd == 4:
        print("删除")
        card = input("card:")
        sql = 'select * from stu_info where card = %s;'
        count = cur.execute(sql, card)
        if count == 1:
            sql = 'delete from stu_info where card = %s;'
            cur.execute(sql, card)
            db.commit()
            print("删除成功")
        else:
            print("查无此卡")
        db.close()

    elif pd == 5:
        print("退出")
        break
    else:
        print("输入错误")
        continue

